A Generic CodeIgniter Function for both Update and Insert

Here is the generic CodeIgniter model function which you can be used to both update and insert data into the database. The function checks primary key validation automatically so it will update the contents if primary key already exists else it will perform insert query.  The MySQL official documentation for insert of a duplicate key update is given below.

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

If you specify, ON DUPLICATE KEY UPDATE and a row is inserted that would cause a duplicate value in an UNIQUE index or, PRIMARY KEY MySQL performs the update of the old row. Now our aim is to create a generic model function for CodeIgniter based on above query. The generic function is given below

 public function updateOnDuplicate($table, $data ) {
     if (empty($table) || empty($data)) return false;
     $duplicate_data = array();
     
     foreach($data AS $key => $value) {
        $duplicate_data[] = sprintf("%s='%s'", $key, addslashes($value));
     }

     $sql = sprintf("%s ON DUPLICATE KEY UPDATE %s", $this->db->insert_string($table, $data), implode(',', $duplicate_data));
     
     $this->db->query($sql);
     return $this->db->insert_id();
}

Note

The second parameter of the function must be given as an associated array.

eg $data = array('name' => $name, 'email' => $email, 'url' => $url);

This is a model function and function calling must be done in the controller but since CodeIgniter is a loosely coupled MVC you can use it in controller too.

Function Explanation

The sprinf function is a PHP core function that will return a formatted string. Eg

<?php
$num = 5;
$location = 'tree';

$format = 'There are %d monkeys in the %s';
echo sprintf($format, $num, $location);
?>

The insert_string is a CodeIgniter query builder function which returns a correctly formatted insert query string. Eg

$data = array('name' => $name, 'email' => $email, 'url' => $url);

$str = $this->db->insert_string('table_name', $data);

The first parameter is the table name, the second is an associative array with the data to be inserted. The above example produces:

INSERT INTO table_name (name, email, url) VALUES ('Rick', '[email protected]', 'example.com')

The implode  Join array elements with a string. Eg

<?php

$array = array('lastname', 'email', 'phone');
$comma_separated = implode(",", $array);

echo $comma_separated; // lastname,email,phone

The insert_id is another CodeIgniter query builder function to insert id number when performing database insert.

So we can see that $sql will produce the same syntax as official MySQL UPDATE ON DUPLICATE syntax. Comment below if you have any doubts or suggestions.